/** 拼装SQL语句工具类，支持MySQL分页查询 by zds 2018-7-18
 * 用法：let query = new SqlQuery(ctx.db);
 * query.select("id,account,nickname,name")
	.from("t_user")
	.whereif("id = ?", tar.userId))
	.andif("nickname like ?", query.like(tar.name))
	.andif("create_time >= ?", tar.start)
	.page(tar.page, tar.page_size)
	.orderBy('~id');
 * 执行查询 let rows = await query.getRowList();
 * 获取记录数 let count = await query.getRowCount();
 */

const MAX_RESULT = 5000;	// 最多返回记录数
const PAGE_SIZE = 20;		// 默认每页显示20条记录

// 判断值是否为空
function isEmpty(value){
	return value === undefined || value === null || value === '';
}

// SQL工具类
function SqlQuery(db) {
	
	let tableColumns;		// 查询表字段
	let tableName;			// 查询表名
	let whereClause = [];	// sql语句缓冲区
	let hasWhere = false;	// 有where语句
	let orderFields;		// 排序字段
	let pageResults = 0;	// 查询时每页显示的记录数
	let offsetResults = 0;	// 跳过记录数
	let group				// 分组查询字段
	this.parameters = [];	// 输入参数
	this.db = db;
	this.pageNumber = 0;	// 分页号

	// 添加主表查询字段，可以添加多个 select 字段
	this.select = function(expression){
		if (tableColumns)
			tableColumns += ',' + expression;
		else
			tableColumns = expression;
		return this;
	}
	// 添加 from 语句，可以添加多个 from 语句
	this.from = function(expression, relation){
		if (tableName){
			tableName += ' LEFT JOIN ' + expression;
			if (relation)
				tableName += ' ON ' + relation;
		} else {
			tableName = expression;
		}
		return this;
	}
	// leftJoin 跟 from 语句一样
	this.leftJoin = this.from;
	
	// 添加 where 条件语句 statement
	this.where = function(expression, value){
		if (expression){
			whereClause.push(expression);
			if (value !== undefined)
				this.parameters.push(value);
			hasWhere = true;
		}
		return this;
	}
	this.whereif = function(expression, value){
		return value ? this.where(expression, value) : this;
	}
	
	// 添加 and 条件语句
	this.and = function(expression, value){
		if (!expression)
			return this;
		if (hasWhere)
			whereClause.push(" AND ");
		else
			hasWhere = true;
		whereClause.push(expression);
		if (value !== undefined)
			this.parameters.push(value);
		return this;
	}
	this.andif = function(expression, value){
		return isEmpty(value) ? this : this.and(expression, value);
	}
	
	// 添加 or 条件语句
	this.or = function(expression, value){
		if (!expression)
			return this;
		if (hasWhere)
			whereClause.push(" OR ");
		else
			hasWhere = true;
		whereClause.push(expression);
		if (value !== undefined)
			this.parameters.push(value);
		return this;
	}
	this.orif = function(expression, value){
		return isEmpty(value) ? this : this.or(expression, value);
	}
	this.like = function(value){
		return isEmpty(value) ? value : '%' + value + '%';
	}
	this.map = function(value, objmap){
		return isEmpty(value) ? value : objmap[value];
	}
	// 按逗号拆分
	this.split = function(value){
		if (isEmpty(value)) return value;
		if (typeof value == "string") return value.split(/\s*,\s*/);
		if (typeof value == "number") return [value];
		return value;
	}

	// 添加排序语句
	this.orderBy = function(expression){
		orderFields = expression;
		
		return this;
	}

	// 添加分页语句
	this.page = function(number, size){
		if (size){
			size = Number(size);
			pageResults = (size > 0 && size <= MAX_RESULT) ? size : PAGE_SIZE;
		} else {
			pageResults = PAGE_SIZE;
		}
		if (number){
			number = Number(number);
			if (number > 0){
				this.pageNumber = number;
				offsetResults = (number - 1)*pageResults;	
			}
		}
		return this;
	}

	// 添加分组查询语句 (传入要分组的字段)
	this.groupBy = function(field){
		this.group = field
		return this
	}

	// 转换为SQL语句
	this.sql = function(){
		let sentence = ["SELECT "];	// 拼装SQL语句
		sentence.push(tableColumns);
		sentence.push(" FROM ");
		sentence.push(tableName);
		if (hasWhere){
			sentence.push(" WHERE ");
			sentence.push(whereClause.join(''));
		}

		if(this.group){
			sentence.push(' group by ')
			sentence.push(this.group)
		}

		if (orderFields != null){
			sentence.push(" ORDER BY ");
			sentence.push(orderFields);
		}
		
		
			
		// 添加 MySQL 分页语句
		if (pageResults > 0){
			sentence.push(" LIMIT ");
			if (offsetResults > 0){
				sentence.push(offsetResults);
				sentence.push(',');
			}
			sentence.push(pageResults);
		}

		
		return sentence.join('');
	}
	// 拼装计算记录总数的SQL语句
	this.totalSQL = function(){
		let sentence = ["SELECT count(*) as num FROM "];
		sentence.push(tableName);
		if (hasWhere){
			sentence.push(" WHERE ");
			sentence.push(whereClause.join(''));
		}
		return sentence.join('');
	}

	// 获取记录数
	this.getRowCount = async function(){
		try	{
			let sql = this.totalSQL();
			let [totalRows] = await db.query(sql, this.parameters);
			if (totalRows.length > 0)
				return totalRows[0].num;
		} catch (err) {
			console.log(err);
		}
		return -1;
	}
	// 查询记录集
	this.getRowList = async function(){
		let sql = this.sql();
		console.log(sql,this.parameters);
		let [results] = await db.query(sql, this.parameters);
		return results;
	}

}

module.exports = SqlQuery